using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SQLite;
using System.Linq;
using System.Net;
using System.Text;
using Dapper;
using SocketTool.Model;
using SocketTool.Model.DataModels;
using SocketTool.Model.DataModels.DbModel;
using io = System.IO;

namespace SocketTool.Dll
{
    public class JsonProtocolsDll
    {
        private static readonly string dbFolderPath =
            io.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData), "SocketTool", "Db");

        private static readonly string dbPath = io.Path.Combine(dbFolderPath, "JsonProtocols.db3");
        private static readonly string connectionString = $"Data Source={dbPath}";

        private SQLiteConnection mSQLiteCon
        {
            get { return new SQLiteConnection(connectionString); }
        }

        /// <summary>
        /// 检查数据库文件
        /// </summary>
        /// <param name="checkError"></param>
        public bool CheckDbFile(out string checkError)
        {
            try
            {
                checkError = string.Empty;
                if (!io.Directory.Exists(dbFolderPath))
                    io.Directory.CreateDirectory(dbFolderPath);

                var dbFileInfo = new io.FileInfo(dbPath);

                #region 检查表字段

                //if (dbFileInfo.Exists && dbFileInfo.Length > 0)
                //{
                //    return CheckDBColmns(out errorMsg);
                //}

                #endregion

                if (dbFileInfo.Exists)
                    return true;

                #region 创建数据库文件

                SQLiteConnection.CreateFile(dbPath);

                #endregion

                using (var sqlCon = mSQLiteCon)
                {
                    if (sqlCon.State != System.Data.ConnectionState.Open)
                        sqlCon.Open();

                    sqlCon.Execute(@"CREATE TABLE `ProtocolInfo` (
                                `ProtocolId`    TEXT,
                                `GroupId`       INTEGER,
                                `ProtocolName`  TEXT,
                                `ProtocolCode`  TEXT,
                                `ProtocolExplan`    TEXT,
                                `ProtocolContent`   TEXT
                            )");
                }

                return true;
            }
            catch (Exception ex)
            {
                checkError = "连接记录表异常";
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine($"检查连接记录表异常：{ex.Message}");
                Console.ResetColor();
                return false;
            }
        }

        /// <summary>
        /// 添加新协议
        /// </summary>
        /// <param name="addModel"></param>
        public bool AddNewProtocolInfo(ProtocolInfoModel addModel)
        {
            try
            {
                using (var sqlCon = this.mSQLiteCon)
                {
                    if(sqlCon.State != ConnectionState.Open)
                        sqlCon.Open();

                    var rows = sqlCon.Execute("INSERT INTO ProtocolInfo (ProtocolId,GroupId,ProtocolName,ProtocolCode,ProtocolExplan,ProtocolContent) VALUES (@ProtocolId,@GroupId,@ProtocolName,@ProtocolCode,@ProtocolExplan,@ProtocolContent)",addModel);
                    return rows > 0;
                }
            }
            catch (Exception ex)
            {
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine($"添加新协议 AddNewProtocolInfo {ex.Message}");
                Console.ResetColor();
                throw ex;
            }
        }

        /// <summary>
        /// 修改协议
        /// </summary>
        /// <param name="protocolId"></param>
        /// <returns></returns>
        public bool UpdateProtocolInfo(ProtocolInfoModel modifyModel)
        {
            try
            {
                using (var sqlCon = this.mSQLiteCon)
                {
                    if(sqlCon.State != ConnectionState.Open)
                        sqlCon.Open();
                    var rows = sqlCon.Execute("UPDATE ProtocolInfo SET ProtocolName=@ProtocolName,ProtocolCode=@ProtocolCode,ProtocolExplan=@ProtocolExplan,ProtocolContent=@ProtocolContent WHERE ProtocolId=@ProtocolId",modifyModel);
                    return rows > 0;
                }
            }
            catch (Exception ex)
            {
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine(ex);
                Console.ResetColor();
                throw ex;
            }
        }

        /// <summary>
        /// 删除协议
        /// </summary>
        /// <param name="protocolId"></param>
        /// <returns></returns>
        public bool DeleteProtocol(string protocolId)
        {
            try
            {
                using (var sqlCon = this.mSQLiteCon)
                {
                    if(sqlCon.State != ConnectionState.Open)
                        sqlCon.Open();
                    var rows = sqlCon.Execute("DELETE FROM ProtocolInfo WHERE ProtocolId=@Id",new {Id = protocolId});
                    return rows > 0;
                }
            }
            catch (Exception ex)
            {
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine(ex);
                Console.ResetColor();
                throw ex;
            }
        }

        /// <summary>
        /// 批量删除协议
        /// </summary>
        /// <param name="protocolList"></param>
        /// <returns></returns>
        public bool DeleteProtocolList(List<string> protocolList)
        {
            try
            {
                string param = string.Empty;
                foreach (var item in protocolList)
                {
                    if(!string.IsNullOrEmpty(param))
                        param+=",";
                    param += $"'{item}'";
                }
                using (var sqlCon = this.mSQLiteCon)
                {
                    if(sqlCon.State != ConnectionState.Open)
                        sqlCon.Open();
                    var rows = sqlCon.Execute($"DELETE FROM ProtocolInfo WHERE ProtocolId IN {param}");
                    return rows > 0;
                }
            }
            catch (Exception ex)
            {
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine(ex);
                Console.ResetColor();
                throw ex;
            }
        }

        /// <summary>
        /// 查询单条协议信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public ProtocolInfoModel QueryProtocol(string id)
        {
            try
            {
                using (var sqlCon = this.mSQLiteCon)
                {
                    if(sqlCon.State != ConnectionState.Open)
                        sqlCon.Open();
                    var rows = sqlCon.QuerySingle<ProtocolInfoModel>("SELECT * FROM ProtocolInfo WHERE ProtocolId=@Id",
                        new {Id = id});
                    return rows;
                }
            }
            catch (Exception ex)
            {
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine(ex);
                Console.ResetColor();
                throw ex;
            }
        }


        /// <summary>
        /// 查询分组下的所有协议信息
        /// </summary>
        /// <param name="groupId"></param>
        /// <returns></returns>
        public List<ProtocolInfoModel> QueryProtocols(int groupId)
        {
            try
            {
                using (var sqlCon = this.mSQLiteCon)
                {
                    if(sqlCon.State != ConnectionState.Open)
                        sqlCon.Open();
                    var rows = sqlCon.Query<ProtocolInfoModel>("SELECT * FROM ProtocolInfo WHERE GroupId=@Id",
                        new {Id = groupId});
                    return rows.ToList();
                }
            }
            catch (Exception ex)
            {
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine(ex);
                Console.ResetColor();
                throw ex;
            }
        }

        /// <summary>
        /// 查询分组下制定名称的协议
        /// </summary>
        /// <param name="name"></param>
        /// <param name="groupId"></param>
        /// <returns></returns>
        public List<ProtocolInfoModel> QueryProtocolsForName(string name, int groupId)
        {
            try
            {
                using (var sqlCon = this.mSQLiteCon)
                {
                    if(sqlCon.State != ConnectionState.Open)
                        sqlCon.Open();

                    var query = sqlCon.Query<ProtocolInfoModel>($"SELECT * FROM ProtocolInfo WHERE GroupId=@Id AND (ProtocolName like '%{name}%' OR ProtocolCode like '%{name}%')", new {Name = name, Id = groupId,Code= name});
                    return query.ToList();
                }
            }
            catch (Exception ex)
            {
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine($"查询协议信息错误 QueryProtocolsForName {ex.Message}");
                Console.ResetColor();
                throw ex;
            }
        }
    }
}